package com.bridgeintelligent.tag.webserver.dim.query.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.bridgeintelligent.tag.utils.DateHelper;
import com.bridgeintelligent.tag.webserver.dim.query.api.AnalysisAggregationResult;
import com.bridgeintelligent.tag.webserver.dim.query.api.AnalysisAggregationRsp;
import com.bridgeintelligent.tag.webserver.dim.query.api.MultiAnalysisAggregationResult;
import com.bridgeintelligent.tag.webserver.system.entity.DimUseRecord;
import com.bridgeintelligent.tag.webserver.system.service.DimUseRecordService;
import com.wayneleo.quickstart.framework.IDGenerator;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.stream.Collectors;

import static javax.servlet.http.HttpServletResponse.SC_OK;

@Api(tags = "画像分析EXCEL导出")
@AllArgsConstructor
@RestController
@Slf4j
@RequestMapping(value = "/api/anaAggExport")
public class AnalysisAggregationExportController {

    private final QueryApiUtils queryApiUtils;

    private DimUseRecordService recordService;

    @ApiOperation(value = "画像分析-导出Excel", notes = "动态聚合导出")
    @PostMapping("/export")
    public void exportAgg(String condition, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            QueryApi queryApi = queryApiUtils.build(QueryApi.class);
            AnalysisAggregationRsp resp =  queryApi.analysisAggregation(condition);
            log.info("返回数据结果："+ JSONUtil.toJsonStr(resp));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);

            List<AnalysisAggregationResult> onedatas = resp.getData();
            List<MultiAnalysisAggregationResult> multidatas = resp.getMultidata();
            List<String> recordList = new ArrayList<>();
            if (!CollectionUtils.isEmpty(onedatas)){
                for (AnalysisAggregationResult data:onedatas) {
                    if (StrUtil.isNotBlank(data.getDimId())){
                        recordList.add(data.getDimId());
                    }
                    createOneAggSheet(wb, commonStyle ,data);
                }
            }
            if (!CollectionUtils.isEmpty(multidatas)){
                for (MultiAnalysisAggregationResult multidata:multidatas) {
                    if (StrUtil.isNotBlank(multidata.getXDimId())){
                        recordList.add(multidata.getXDimId());
                    }
                    if (StrUtil.isNotBlank(multidata.getYDimId())){
                        recordList.add(multidata.getYDimId());
                    }
                    createMoreAggSheet(wb, commonStyle ,multidata);
                }
            }
            try {
                if (CollUtil.isNotEmpty(recordList)){
                    String dims = recordList.stream().collect(Collectors.joining(","));
                    recordService.addRecord(DimUseRecord.of().setId(IDGenerator.gen()).setDataType("1").setDimId(dims).setCreateTime(DateHelper.currentDateTime()));
                }
            }catch (Exception e){
                log.error("保存标签下载记录异常",e);
            }
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName = "画像分析-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("画像分析导出异常", e);
                throw new RuntimeException("画像分析导出异常");
            }
        }catch (Exception e){
            log.error("画像分析导出异常!", e);
            throw new RuntimeException("画像分析导出异常!");
        }

    }

    //==========================呵呵多维数据处理============================================================
    public void createMoreAggSheet(XSSFWorkbook wb, CellStyle commonStyle,MultiAnalysisAggregationResult excelModel) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(excelModel.getXDimName() + "-" + excelModel.getYDimName());
            List<MultiAnalysisAggregationResult.X> dataX = excelModel.getData();
            if (CollectionUtils.isEmpty(dataX)) {
                return;
            }

            //获取y的title
            List<String> yTitles = getTitlesY(excelModel.getData(), excelModel.getYClassify());
            //获取动态展示指标 ["custNum","age|min","age|max"]
            List<String> fields = excelModel.getFields();
            //获取动态字段中文名称 <"age","年龄">
            Map<String, String> filedsMap = excelModel.getFieldsMap();
            int m = yTitles.size();
            int n = fields.size();
            // 行号
            int rowNum = 0;
            //设置列宽
            for (int i = 0; i < m * n + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);

            List<String> head1 = new ArrayList<>(m * n + 1);
            head1.add(excelModel.getXDimName() + "/" + excelModel.getYDimName());
            for (int i = 0; i < m; i++) {
                head1.add(yTitles.get(i));
                for (int j = 0; j < n - 1; j++) {
                    head1.add("");
                }
            }
            String[] rowSecond = head1.stream().toArray(String[]::new);
            for (int i = 0; i < rowSecond.length; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(rowSecond[i]);
                tempCell.setCellStyle(commonStyle);
            }
            //第2行
            Row r1 = sheet.createRow(rowNum++);
            List<String> head2 = new ArrayList<>(m * n + 1);
            for (int i = 0; i < m; i++) {
                for (String str : fields) {
                    if (str.equals("custNum")) {
                        head2.add("客户数");
                    } else {
                        String[] split = str.split("\\|");
                        head2.add(filedsMap.get(split[0]) + "(" + split[1] + ")");
                    }
                }
            }
            String[] rowSecond2 = head2.stream().toArray(String[]::new);
            for (int i = 0; i < rowSecond2.length; i++) {
                Cell tempCell = r1.createCell(i + 1);
                tempCell.setCellValue(rowSecond2[i]);
                tempCell.setCellStyle(commonStyle);
            }
            //合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            if(n > 1){
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, n));
            }
            int k = 1;
            if (n >1 ){ //一个单元格合并会有问题
                for (int i = 0; i < m - 1; i++) {
                    k = k + n;
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, k, k + n - 1));
                }
            }
            //循环填充数据
            for (MultiAnalysisAggregationResult.X x : dataX) {
                Row row = sheet.createRow(rowNum++);
                //获取rowData数据
                List<String> rowData = getRowData(x.getTitle(), x.getYs(), yTitles, fields);
                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell = row.createCell(i);
                    tempCell.setCellValue(rowData.get(i));
                    tempCell.setCellStyle(commonStyle);
                }
            }
        } catch (Exception e) {
            log.error("导出" + excelModel.getXDimName() + "-" + excelModel.getYDimName() + "时出现异常", e);
        }
    }

    public CellStyle setStyle(XSSFWorkbook wb) {
        //表头字体
        Font headerFont = wb.createFont();
        headerFont.setFontName("宋体");
        headerFont.setFontHeightInPoints((short) 18);
        headerFont.setBold(true);
        headerFont.setColor(Font.COLOR_NORMAL);
        //正文字体
        Font contextFont = wb.createFont();
        contextFont.setFontName("宋体");
        contextFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        //表头样式，左右上下居中
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFont(headerFont);
        // 左右居中
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        // 上下居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setLocked(true);
        // 自动换行
        headerStyle.setWrapText(false);
        //单元格样式，左右上下居中 边框
        CellStyle commonStyle = wb.createCellStyle();
        commonStyle.setFont(contextFont);
        // 左右居中
        commonStyle.setAlignment(HorizontalAlignment.CENTER);
        // 上下居中
        commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        commonStyle.setLocked(true);
        // 自动换行
        commonStyle.setWrapText(false);
        //单元格样式，左右上下居中 边框
        CellStyle commonWrapStyle = wb.createCellStyle();
        commonWrapStyle.setFont(contextFont);
        //单元格样式，竖向 边框
        CellStyle verticalStyle = wb.createCellStyle();
        verticalStyle.setFont(contextFont);
        CellStyle commonStyleNoBorder = wb.createCellStyle();
        commonStyleNoBorder.setFont(contextFont);
        commonStyleNoBorder.setLocked(true);
        // 自动换行
        commonStyleNoBorder.setWrapText(false);
        CellStyle alignLeftStyle = wb.createCellStyle();
        alignLeftStyle.setFont(contextFont);
        alignLeftStyle.setLocked(true);
        // 自动换行
        alignLeftStyle.setWrapText(false);
        //单元格样式，左对齐 无边框
        CellStyle alignLeftNoBorderStyle = wb.createCellStyle();
        alignLeftNoBorderStyle.setFont(contextFont);
        alignLeftNoBorderStyle.setLocked(true);
        // 自动换行
        alignLeftNoBorderStyle.setWrapText(false);
        //单元格样式，右对齐
        CellStyle alignRightStyle = wb.createCellStyle();
        alignRightStyle.setFont(contextFont);
        alignRightStyle.setLocked(true);
        // 自动换行
        alignRightStyle.setWrapText(false);
        return commonStyle;
    }

    public List<String> getRowData(String xTtile, List<MultiAnalysisAggregationResult.Y> ys, List<String> yTitles, List<String> fields) {
        //计算每行需要的数据长度
        List<String> rowData = new ArrayList<>(yTitles.size() + fields.size() + 1);
        rowData.add(xTtile);
        //处理y与info的关系
        Map<String, List<MultiAnalysisAggregationResult.Info>> y2info = ys.stream().collect(Collectors.toMap(k -> k.getTitle(), v -> v.getInfo()));
        //处理y与count的关系
        Map<String, Long> y2count = ys.stream().collect(Collectors.toMap(k -> k.getTitle(), v -> v.getCount()));
        for (String title : yTitles) {
            if (!y2info.containsKey(title)) {
                for (String field : fields) {
                    rowData.add("");
                }
            } else {
                List<MultiAnalysisAggregationResult.Info> infos = y2info.get(title);
                //将infos处理成指标和值的关系
                Map<String, String> infoMap = new HashMap<>();
                infos.forEach(info -> {
                    infoMap.put(info.getField() + "|max", info.getMaxValue());
                    infoMap.put(info.getField() + "|min", info.getMinValue());
                    infoMap.put(info.getField() + "|avg", info.getAvgValue());
                    infoMap.put(info.getField() + "|sum", info.getSumValue());
                });
                for (String field : fields) {
                    if ("custNum".equals(field)) {
                        rowData.add(String.valueOf(y2count.get(title)));
                    } else {
                        rowData.add(infoMap.get(field));
                    }
                }

            }
        }
        return rowData;
    }

    public List<String> getTitlesY(List<MultiAnalysisAggregationResult.X> datax, String classify) {
        List<MultiAnalysisAggregationResult.Y> yList = new ArrayList<>();
        //循环遍历X，取出y集合
        datax.forEach(m -> yList.addAll(m.getYs()));
        //取出y的title并去重
        List<String> yTitle = yList.stream().map(m -> m.getTitle()).distinct().collect(Collectors.toList());
        //根据标签类型排序
        sortY(classify, yTitle);
        return yTitle;
    }


    /**
     * Y维度根据title排序
     *
     * @param classify 类型
     * @param data     数据
     */
    public void sortY(String classify, List<String> data) {
        if (StringUtils.equals(classify, "03")) {
            data.sort((o1, o2) -> {
                Double v1 = getValue(o1);
                Double v2 = getValue(o2);
                return v1.compareTo(v2);
            });
        }
        if (StringUtils.equals(classify, "05")) {
            data.sort((o1, o2) -> {
                String title1 = o1;
                String title2 = o2;
                if (StringUtils.contains(title1, "-∞") || StringUtils.contains(title2, "+∞")) {
                    return -1;
                }
                if (StringUtils.contains(title1, "+∞") || StringUtils.contains(title2, "-∞")) {
                    return 1;
                }
                return StringUtils.compare(title1, title2);
            });
        }
    }

    public Double getValue(String D1) {
        String[] array = D1.split("~");
        if (D1.indexOf("-") == 0) {
            if (StringUtils.equals("-∞", array[0])) {
                return Double.MIN_VALUE;
            }
            return Double.parseDouble(array[0]);
        } else {
            if (StringUtils.equals("+∞", array[0]) || StringUtils.equals("∞", array[0])) {
                return Double.MAX_VALUE;
            }
            return Double.parseDouble(array[0]);
        }
    }

    public MultiAnalysisAggregationResult makeMoreData() {

        List<MultiAnalysisAggregationResult.Info> infos1 = new ArrayList<>();
        infos1.add(MultiAnalysisAggregationResult.Info.of().setField("age").setMinValue("0").setMaxValue("50").setAvgValue("25"));
        infos1.add(MultiAnalysisAggregationResult.Info.of().setField("money").setMinValue("0.1").setMaxValue("20.0").setAvgValue("10.0"));

        List<MultiAnalysisAggregationResult.Info> infos2 = new ArrayList<>();
        infos2.add(MultiAnalysisAggregationResult.Info.of().setField("age").setMinValue("50").setMaxValue("100").setAvgValue("75"));
        infos2.add(MultiAnalysisAggregationResult.Info.of().setField("money").setMinValue("20").setMaxValue("30.0").setAvgValue("25.0"));

        List<MultiAnalysisAggregationResult.Y> ys1 = new ArrayList<>();
        ys1.add(MultiAnalysisAggregationResult.Y.of().setTitle("0~50").setCount(20).setInfo(infos1));
        ys1.add(MultiAnalysisAggregationResult.Y.of().setTitle("50~100").setCount(10).setInfo(infos2));
        ys1.add(MultiAnalysisAggregationResult.Y.of().setTitle("100~150").setCount(10).setInfo(infos2));

        List<MultiAnalysisAggregationResult.Info> infos3 = new ArrayList<>();
        infos3.add(MultiAnalysisAggregationResult.Info.of().setField("age").setMinValue("1").setMaxValue("49").setAvgValue("26"));
        infos3.add(MultiAnalysisAggregationResult.Info.of().setField("money").setMinValue("0.2").setMaxValue("20.1").setAvgValue("11.0"));

        List<MultiAnalysisAggregationResult.Info> infos4 = new ArrayList<>();
        infos4.add(MultiAnalysisAggregationResult.Info.of().setField("age").setMinValue("51").setMaxValue("99").setAvgValue("76"));
        infos4.add(MultiAnalysisAggregationResult.Info.of().setField("money").setMinValue("20.8").setMaxValue("30.5").setAvgValue("27.0"));

        List<MultiAnalysisAggregationResult.Y> ys2 = new ArrayList<>();
        ys2.add(MultiAnalysisAggregationResult.Y.of().setTitle("0~50").setCount(20).setInfo(infos3));
        ys2.add(MultiAnalysisAggregationResult.Y.of().setTitle("70~100").setCount(10).setInfo(infos4));

        List<MultiAnalysisAggregationResult.X> xs = new ArrayList<>();
        xs.add(MultiAnalysisAggregationResult.X.of().setTitle("X").setCount(10).setYs(ys1));
        xs.add(MultiAnalysisAggregationResult.X.of().setTitle("F").setCount(20).setYs(ys2));

        List<String> fields = new ArrayList<>();
        fields.add("custNum");
        fields.add("age|max");
        fields.add("age|min");
        fields.add("money|min");
        Map<String, String> fieldsMap = new HashMap<>();
        fieldsMap.put("age", "年龄");
        fieldsMap.put("money", "金额");
        return MultiAnalysisAggregationResult.of().setData(xs).setXDimId("sex").setXDimName("性别").setXClassify("02").setYDimId("age").setYDimName("年龄").setYClassify("03").setFields(fields).setFieldsMap(fieldsMap);
    }

    //========================单维数据处理=====================================================================================================================
    public void createOneAggSheet(XSSFWorkbook wb, CellStyle commonStyle ,AnalysisAggregationResult excelModel) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(excelModel.getDimName());
            List<AnalysisAggregationResult.R> dataR = excelModel.getData();
            if (CollectionUtils.isEmpty(dataR)) {
                return;
            }
            //获取动态展示指标 ["custNum","age|min","age|max"]
            List<String> fields = excelModel.getFields();
            //获取动态字段中文名称 <"age","年龄">
            Map<String, String> filedsMap = excelModel.getFieldsMap();
            //获取y的title
            List<String> yTitles = getTitlesY1(fields, filedsMap);
            int m = yTitles.size();
            // 行号
            int rowNum = 0;
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            Cell tempCell = r0.createCell(0);
            tempCell.setCellValue(excelModel.getDimName() + "/指标值");
            tempCell.setCellStyle(commonStyle);
            for (int i = 0; i < yTitles.size(); i++) {
                Cell tempCell1 = r0.createCell(i + 1);
                tempCell1.setCellValue(yTitles.get(i));
                tempCell1.setCellStyle(commonStyle);
            }

            //循环填充数据
            for (AnalysisAggregationResult.R r : dataR) {
                Row row = sheet.createRow(rowNum++);
                //获取rowData数据
                List<String> rowData = getRowData1(r,yTitles, fields);
                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
            }
        } catch (Exception e) {
            log.error("导出" + excelModel.getDimName() + "时出现异常", e);
        }
    }

    public List<String> getRowData1(AnalysisAggregationResult.R r,List<String> yTitles, List<String> fields) {
        //计算每行需要的数据长度
        List<String> rowData = new ArrayList<>(yTitles.size() + 1);
        rowData.add(r.getTitle());
        //将infos处理成指标和值的关系
        List<AnalysisAggregationResult.Info> infos = r.getInfo();
        Map<String, String> infoMap = new HashMap<>();
        infos.forEach(info -> {
            infoMap.put(info.getField() + "|max", info.getMaxValue());
            infoMap.put(info.getField() + "|min", info.getMinValue());
            infoMap.put(info.getField() + "|avg", info.getAvgValue());
            infoMap.put(info.getField() + "|sum", info.getSumValue());
        });
        for (String field : fields) {
            if ("custNum".equals(field)) {
                rowData.add(String.valueOf(r.getCount()));
            } else {
                rowData.add(infoMap.get(field));
            }
        }
        return rowData;
    }

    public List<String> getTitlesY1(List<String> fields,Map<String,String> fieldsMap){
        List<String> yTitle = new ArrayList<>();
        fields.forEach(m->{
            if ("custNum".equals(m)){
                yTitle.add("客户数");
            }else{
                String[] split = m.split("\\|");
                yTitle.add(fieldsMap.get(split[0])+"("+split[1]+")");
            }

        });
        return yTitle;
    }
}
